import pandas as pd
import random
import numpy as np

if __name__ == '__main__':
    # 读入数据
    df = pd.read_csv('book.csv', usecols=[0, 1, 2, 3, 4])
    # 定义sql格式
    sql = 'insert into rented_book_inventory(book_name, icon, rating, price, description, category_id, author) values("{0}", "{1}", {2}, {3}, "{4}", {5}, "{6}");\n'
    # 标题
    name_list = df['title']
    # 图标
    icon_list = df['src']
    # 得分
    rating_list = df['score']
    # 价格
    price_list = df['price']
    # 简介
    description_list = df['info']
    for i in range(df.shape[0]):
        # 标题
        name = name_list[i].strip()
        # 图标
        icon = icon_list[i].strip()
        # 得分，如果得分为空，则得分为0.0
        rating = rating_list[i]
        if (np.nan == rating or rating is None):
            rating = 0.0
        # 价格
        price = price_list[i].split("元")[0]
        if (price == "免费"):
            price = 0.00
        else:
            price = float(price)
        # 简介，简介中可能会出现双引号出现的情况，直接跳过这一行的数据
        description = description_list[i] if len(description_list[i]) < 200 else description_list[i][:200]
        if "\"" in description:
            continue
        # 分类id，随机生成
        category_id = random.randint(1, 10)
        # 作者，随机生成
        author = random.choice(["安徒生", "金浩然", "储成龙", "刘宛晴", "朱子杰", "王伟", "莫言", "石头", "魔方", "猪猪", "张跃平", "刘汝佳", "马丽芳", "曹文轩", "成超", "鲁迅", "周树人", "李大钊", "陈独秀"])
        with open('data_rent.sql', 'a+', encoding='utf-8') as fp:
            fp.write(sql.format(name, icon, rating, price, description, category_id, author))
